/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import POJO.HoaDon;
import POJO.KhachHang;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Hồng
 */
public class HoaDonDao {

	public static ArrayList<HoaDon> layDanhSach() {
		ArrayList<HoaDon> ds = new ArrayList<HoaDon>();
		String sql = "SELECT  MaHD, NgayLap, TriGia, SoTienGiam, TrangThai, MaNVPhuTrach, MaKH   FROM  HoaDon ";

		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				int ma = rs.getInt("MaHD");
				Date ngaylap = rs.getDate("NgayLap");
				Double trigia = rs.getDouble("TriGia");
				Double tiengiam = rs.getDouble("SoTienGiam");
				int trangthai = rs.getInt("TrangThai");
				int manv = rs.getInt("MaNVPhuTrach");
				int makh = rs.getInt("MaKH");
				HoaDon tmp = new HoaDon(ma, ngaylap, trigia, tiengiam, trangthai, manv, makh);
				ds.add(tmp);
			}
		} catch (Exception ex) {
			Logger.getLogger(HoaDonDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}

	public static ArrayList<HoaDon> layDanhSach(int soDong, int trangSo) {
		ArrayList<HoaDon> ds = new ArrayList<HoaDon>();
		//String sql = "SELECT  MaHD, NgayLap, TriGia, SoTienGiam, TrangThai, MaNVPhuTrach, MaKH   FROM  HoaDon ";

		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBangPhanTrang("hoaDon", "MaHD", soDong, trangSo);
			while (rs.next()) {
				int ma = rs.getInt("MaHD");
				Date ngaylap = rs.getDate("NgayLap");
				Double trigia = rs.getDouble("TriGia");
				Double tiengiam = rs.getDouble("SoTienGiam");
				int trangthai = rs.getInt("TrangThai");
				int manv = rs.getInt("MaNVPhuTrach");
				int makh = rs.getInt("MaKH");
				HoaDon tmp = new HoaDon(ma, ngaylap, trigia, tiengiam, trangthai, manv, makh);
				ds.add(tmp);
			}
		} catch (Exception ex) {
			Logger.getLogger(HoaDonDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}

	public static ArrayList<HoaDon> timKiemByMaNV(int manv) {
		ArrayList<HoaDon> ds = new ArrayList<HoaDon>();

		String sql = "SELECT * FROM HoaDon WHERE MaNV LIKE '%" + manv + "%'";
		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				int ma = rs.getInt("MaHD");
				Date ngaylap = rs.getDate("NgayLap");
				Double trigia = rs.getDouble("TriGia");
				Double tiengiam = rs.getDouble("SoTienGiam");
				int trangthai = rs.getInt("TrangThai");
				int ma_nv = rs.getInt("MaNVPhuTrach");
				int makh = rs.getInt("MaKH");
				HoaDon tmp = new HoaDon(ma, ngaylap, trigia, tiengiam, trangthai, ma_nv, makh);
				ds.add(tmp);
			}
		} catch (Exception ex) {
			Logger.getLogger(HoaDonDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}

	public static ArrayList<HoaDon> layDanhSachHoaDonCuaKhachhang(KhachHang tmp) {
		ArrayList<HoaDon> ds = new ArrayList<HoaDon>();
		String sql = "select * from hoaDon hd  where hd.MaKH = " + tmp.getMaKH() + "order by hd.MaHD desc;";

		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				int maHD = rs.getInt("MaHD");
				Date ngayLap = rs.getDate("NgayLap");
				Float triGia = rs.getFloat("TriGia");
				Float soTienGiam = rs.getFloat("SoTienGiam");
				int trangThai = rs.getInt("TrangThai");
				int maNhanVien = rs.getInt("MaNVPhuTrach");
				int maKH = rs.getInt("MaKH");
				HoaDon tmp1 = new HoaDon(maHD, ngayLap, triGia, soTienGiam, trangThai, maNhanVien, maKH);
				ds.add(tmp1);
			}
		} catch (Exception ex) {
			Logger.getLogger(KhachHang.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}

	public static ArrayList<HoaDon> timKiemByNgay(String ngay) {
		ArrayList<HoaDon> ds = new ArrayList<HoaDon>();

		String sql = "SELECT * FROM HoaDon WHERE Day(NgayLap) LIKE '%" + ngay + "%'";
		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				int ma = rs.getInt("MaHD");
				Date ngaylap = rs.getDate("NgayLap");
				Double trigia = rs.getDouble("TriGia");
				Double tiengiam = rs.getDouble("SoTienGiam");
				int trangthai = rs.getInt("TrangThai");
				int ma_nv = rs.getInt("MaNVPhuTrach");
				int makh = rs.getInt("MaKH");
				HoaDon tmp = new HoaDon(ma, ngaylap, trigia, tiengiam, trangthai, ma_nv, makh);
				ds.add(tmp);
			}
		} catch (Exception ex) {
			Logger.getLogger(HoaDonDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}

	public static int layMaHDMax() {
		String sql = "select hd.MaHD  from HoaDon hd where hd.MaHD >= all(select hd1.MaHD from HoaDon hd1 ); ";
		KetNoi cn = null;
		ResultSet rs = null;
		int n = 0;

		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				n = rs.getInt("MaHD");
			}
		} catch (Exception ex) {
			Logger.getLogger(HoaDonDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return n;
	}

	public static int laySoLanMua(int maKH) {
		String sql = "select count(HD.MaHD) as SoLanMua from HoaDon HD where MaKH = " + maKH;

		KetNoi cn = null;
		ResultSet rs = null;
		int n = 0;

		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				n = rs.getInt("SoLanMua");
			}
		} catch (Exception ex) {
			Logger.getLogger(HoaDonDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return n;
	}

	public static int laySoDong() {
		String sql = "select count(MaHD) as COU from HoaDon ";
		KetNoi cn = null;
		ResultSet rs = null;
		int n = 0;

		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				n = rs.getInt("COU");
			}
		} catch (Exception ex) {
			Logger.getLogger(HoaDonDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return n;
	}

	public static boolean them(HoaDon hd) {
		boolean kq = false;
		int mahd = HoaDonDao.layMaHDMax();
		mahd = mahd + 1;
		hd.setMaHD(mahd);
		String sql = "INSERT INTO  HoaDon  ( MaHD, NgayLap, TriGia, SoTienGiam, TrangThai, MaNVPhuTrach, MaKH ) VALUES ";
		int nam = (Integer) hd.getNgayLap().getYear();
		int thang = (Integer) hd.getNgayLap().getMonth() + 1;
		int ng = (Integer) hd.getNgayLap().getDate();
		String ngay = "'" + nam + "-" + thang + "-" + ng + "'";
		String s = String.format("( %d, %s, %f, %f, %d, %d, %d);",
			 hd.getMaHD(),
			 ngay,
			 hd.getTriGia(),
			 hd.getSoTienGiam(),
			 hd.getTrangThai(),
			 hd.getMaNVPhuTrach(),
			 hd.getMaKH());
		sql = sql + s;
		KetNoi cn = null;
		int n;
		n = -1;
		try {
			cn = new KetNoi();
			n = cn.thucThi(sql);
		} catch (SQLException ex) {
			Logger.getLogger(HoaDonDao.class.getName()).log(Level.SEVERE, null, ex);
		}
		if (n == 1) {
			kq = true;
		}
		cn.dongKetNoi();
		return kq;
	}

	public static boolean xoa(int maHD) {
		boolean kq = false;
		String str = "if not exists (select ct.MaHD from  ctHoaDon ct where ct.MaHd =" + maHD
			 + ") and not exists (select px.MaHD from PXuatKho px where px.MaHD = " + maHD;
		String sql = String.format(str + ") DELETE FROM HoaDon WHERE MaHD = %d", maHD);
		KetNoi cn = null;
		int n;
		n = -1;
		try {
			cn = new KetNoi();
			n = cn.thucThi(sql);
		} catch (SQLException ex) {
			Logger.getLogger(HoaDonDao.class.getName()).log(Level.SEVERE, null, ex);
		}
		if (n == 1) {
			kq = true;
		}
		cn.dongKetNoi();
		return kq;
	}

	public static boolean capNhat(HoaDon tmp) {
		boolean kq = false;
		String sql = String.format("UPDATE HoaDon "
			 + "SET "
			 + "NgayLap = '%s', "
			 + "TriGia = %f, "
			 + "SoTienGiam = %f, "
			 + "TrangThai = %d, "
			 + "MaNVPhuTrach = %d, "
			 + "MaKH = %d "
			 + "WHERE MaHD = %d; ", tmp.getNgayLap(), tmp.getTriGia(), tmp.getSoTienGiam(), tmp.getTrangThai(), tmp.getMaNVPhuTrach(), tmp.getMaKH(), tmp.getMaHD());
		KetNoi cn = null;
		int n;
		n = -1;
		try {
			cn = new KetNoi();
			n = cn.thucThi(sql);
		} catch (SQLException ex) {
			Logger.getLogger(HoaDonDao.class.getName()).log(Level.SEVERE, null, ex);
		}
		if (n == 1) {
			kq = true;
		}
		cn.dongKetNoi();
		return kq;
	}

	public static boolean capNhatTrangThai(HoaDon tmp) {
		boolean kq = false;
		String sql = String.format("UPDATE HoaDon "
			 + "SET "
			 + "TrangThai = %d, "
			 + "MaNVPhuTrach = %d, "
			 + "MaKH = %d "
			 + "WHERE MaHD = %d; ", tmp.getTrangThai(), tmp.getMaNVPhuTrach(), tmp.getMaKH(), tmp.getMaHD());
		KetNoi cn = null;
		int n;
		n = -1;
		try {
			cn = new KetNoi();
			n = cn.thucThi(sql);
		} catch (SQLException ex) {
			Logger.getLogger(HoaDonDao.class.getName()).log(Level.SEVERE, null, ex);
		}
		if (n == 1) {
			kq = true;
		}
		cn.dongKetNoi();
		return kq;
	}

	public static HoaDon layByMa(int mahd) {
		ArrayList<HoaDon> ds = new ArrayList<HoaDon>();
		HoaDon tmp = null;
		try {
			String sql = "SELECT * FROM HoaDon WHERE MaHD =" + mahd;
			KetNoi helper = new KetNoi();
			ResultSet rs = helper.docBang(sql);
			if (rs.next()) {
				int ma = rs.getInt("MaHD");
				Date ngaylap = rs.getDate("NgayLap");
				Double trigia = rs.getDouble("TriGia");
				Double tiengiam = rs.getDouble("SoTienGiam");
				int trangthai = rs.getInt("TrangThai");
				int ma_nv = rs.getInt("MaNVPhuTrach");
				int makh = rs.getInt("MaKH");
				tmp = new HoaDon(ma, ngaylap, trigia, tiengiam, trangthai, ma_nv, makh);
				ds.add(tmp);
			}
			helper.dongKetNoi();
		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		}
		return tmp;
	}

	public static ArrayList<HoaDon> layByMaKH(int maKH) {
		ArrayList<HoaDon> ds = new ArrayList<HoaDon>();
		HoaDon tmp = null;
		try {
			String sql = "SELECT * FROM HoaDon WHERE MaKH =" + maKH;
			KetNoi helper = new KetNoi();
			ResultSet rs = helper.docBang(sql);
			if (rs.next()) {
				int ma = rs.getInt("MaHD");
				Date ngaylap = rs.getDate("NgayLap");
				Double trigia = rs.getDouble("TriGia");
				Double tiengiam = rs.getDouble("SoTienGiam");
				int trangthai = rs.getInt("TrangThai");
				int ma_nv = rs.getInt("MaNVPhuTrach");
				int makh = rs.getInt("MaKH");
				tmp = new HoaDon(ma, ngaylap, trigia, tiengiam, trangthai, ma_nv, makh);
				ds.add(tmp);
			}
			helper.dongKetNoi();
		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		}
		return ds;
	}

	public static KhachHang layKhachHangByMaHoaDon(int MaHD) {
		KhachHang tmp = new KhachHang();
		KetNoi cn = null;
		ResultSet rs = null;
		String sql = "Select kh.* from KhachHang kh, HoaDon hd where hd.MaKH = kh.MaKH and hd.MaHD = " + MaHD;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			if (rs.next()) {
				int ma = rs.getInt("MaKH");
				String ten = rs.getString("TenKH");
				String diachi = rs.getString("DiaChi");
				String dienthoai = rs.getString("DienThoai");
				tmp = new KhachHang(ma, ten, diachi, dienthoai);
			}

		} catch (Exception e) {
			Logger.getLogger(HoaDonDao.class.getName()).log(Level.SEVERE, null, e);

		} finally {
			cn.dongKetNoi();
		}
		return tmp;
	}
}
